from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import relationship

from ..ext import Base


# 单表
class Dans(Base):
    __tablename__ = 'dans'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer, nullable=False)


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(120), unique=True, nullable=False)
    password = Column(String(128), nullable=False)

    # 与Profile建立一对一关系
    profile = relationship("Profile", uselist=False, back_populates="user")


class Profile(Base):
    __tablename__ = 'profiles'

    id = Column(Integer, primary_key=True, index=True)
    bio = Column(String)
    avatar_url = Column(String)

    # 与User建立一对一关系
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), unique=True, nullable=False)
    user = relationship("User", back_populates="profile", foreign_keys=[user_id], remote_side=[User.id])


class Article(Base):
    __tablename__ = 'articles'

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, nullable=False)
    content = Column(String, nullable=False)

    # 与Comment建立一对多关系
    comments = relationship("Comment", back_populates="article")


class Comment(Base):
    __tablename__ = 'comments'

    id = Column(Integer, primary_key=True, index=True)
    content = Column(String, nullable=False)

    # 指定外键，与Article建立关联
    article_id = Column(Integer, ForeignKey('articles.id'), nullable=False)

    # 与Article建立关联，使用back_populates指定反向关系
    article = relationship("Article", back_populates="comments")


class Student(Base):
    __tablename__ = 'students'

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)

    # 与Course建立多对多关系
    courses = relationship("Course", secondary="enrollments", back_populates="students")


class Course(Base):
    __tablename__ = 'courses'

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, nullable=False)

    # 与Student建立多对多关系
    students = relationship("Student", secondary="enrollments", back_populates="courses")


enrollments = Table('enrollments', Base.metadata,
                    Column('student_id', Integer, ForeignKey('students.id'), primary_key=True),
                    Column('course_id', Integer, ForeignKey('courses.id'), primary_key=True)
                    )
